FTD Over Time Case Study

Author

Pavlina Novakova

Goal: Provide information about First Time Deposits (FTD) over time on client level.

FTD is calculated as the first deposit a client made across all of their logins.

Code
import pandas as pd
import numpy as np

import plotly.express as px
import plotly.graph_objects as go
from plotly.subplots import make_subplots

import plotly.io as pio
pio.renderers.default = "notebook"
Code
daily_ftd = pd.read_csv('./../../data/report/daily_ftd.csv', index_col=0, parse_dates=['ftd_date'])
monthly_ftd = pd.read_csv('./../../data/report/monthly_ftd.csv', index_col=0)
forecast_ftd = pd.read_csv('./../../data/report/forecast_daily_ftd.csv', index_col=0, parse_dates=['ftd_date'])

1. FTD Key Metrics (Live Registrations Only)

Metric Value
Total FTDs 4,867
Total FTD Volume $2,711,437 USD
Average FTD $557 USD
Median FTD $316 USD
FTD Conversion Rate 91.4% (4,867 / 5,325 clients)

FTD amounts are right-skewed (mean >> median), indicating a small number of high-value first deposits pulling the average up. Only Full and Light registrations are included.

2. FTD Over Time - Daily

Code
fig = make_subplots(rows=2, cols=1, subplot_titles=('Daily FTD Count', 'Daily FTD Volume (USD)'))

fig.add_trace(go.Scatter(x=daily_ftd['ftd_date'], y=daily_ftd['count'], 
                         mode='lines', name='Count'), row=1, col=1)
fig.add_trace(go.Scatter(x=daily_ftd['ftd_date'], y=daily_ftd['volume'], 
                         mode='lines', name='Volume'), row=2, col=1)

fig.update_layout(height=500, showlegend=False)
fig.update_yaxes(title_text='Count', row=1, col=1)
fig.update_yaxes(title_text='USD', row=2, col=1)
fig.show()

Daily FTD count shows high variability with weekday/weekend patterns. Volume follows a similar shape but with more pronounced spikes from individual high-value deposits.

3. FTD Over Time - Monthly

Code
fig = px.bar(monthly_ftd, x='ftd_month', y='count',
             title='Monthly FTD Count',
             labels={'ftd_month': 'Month', 'count': 'FTD Count'})
fig.update_layout(height=400)
fig.show()

fig = px.bar(monthly_ftd, x='ftd_month', y='volume',
             title='Monthly Volume FTD Amount (USD)',
             labels={'ftd_month': 'Month', 'volume': 'Volume (USD)'})
fig.update_layout(height=400)
fig.show()

display(monthly_ftd[['ftd_month', 'count', 'volume', 'avg_amount']])
ftd_month count volume avg_amount
0 2025-01 876 487815.769037 556.867316
1 2025-02 666 429361.021236 644.686218
2 2025-03 358 230712.599879 644.448603
3 2025-04 402 299541.733613 745.128691
4 2025-05 357 252317.334185 706.771244
5 2025-06 370 158618.551086 428.698787
6 2025-07 252 124290.961312 493.218100
7 2025-08 756 291496.712857 385.577663
8 2025-09 513 247469.630934 482.396941
9 2025-10 211 129346.309601 613.015685
10 2025-11 106 60465.904713 570.433063

Monthly FTD count is highest in January (876) and August (756). Later months show fewer FTDs as the observation window narrows (data ends Nov 2025). Average FTD amount fluctuates between $386–$745/month with no clear trend.

4. FTD by Registration Type

Type FTD Count Avg FTD (USD) Share
Full 4,504 $574 92.5%
Light 363 $344 7.5%

Full registrations account for the vast majority of FTDs with a higher average deposit. Light registrations have lower average FTD amounts.

5. FTD Forecast (Next 30 Days)

Code
fig = make_subplots(rows=2, cols=1, subplot_titles=('FTD Count: Actual + Forecast', 'FTD Volume: Actual + Forecast'))

fig.add_trace(go.Scatter(x=daily_ftd['ftd_date'], y=daily_ftd['count'], 
                         mode='lines', name='Actual'), row=1, col=1)
fig.add_trace(go.Scatter(x=forecast_ftd['ftd_date'], y=forecast_ftd['count'], 
                         mode='lines', name='Forecast', line=dict(dash='dash', color='red')), row=1, col=1)

fig.add_trace(go.Scatter(x=daily_ftd['ftd_date'], y=daily_ftd['volume'], 
                         mode='lines', name='Actual', showlegend=False), row=2, col=1)
fig.add_trace(go.Scatter(x=forecast_ftd['ftd_date'], y=forecast_ftd['volume'], 
                         mode='lines', name='Forecast', showlegend=False, line=dict(dash='dash', color='red')), row=2, col=1)

fig.update_layout(height=500)
fig.update_yaxes(title_text='Count', row=1, col=1)
fig.update_yaxes(title_text='USD', row=2, col=1)
fig.show()

display(f'30-day forecast: {forecast_ftd["count"].sum():.0f} FTDs, ${forecast_ftd["volume"].sum():,.0f} volume')
'30-day forecast: 35 FTDs, $19,761 volume'

Moving average models were compared (windows 3, 5, 7, 14, 21). MA(3) performed best with MAE = 3.00 and RMSE = 3.54. The MA(3) rolling forecast for the next 30 days converges to ~1.2 FTDs/day (~$650/day), producing a conservative estimate of 35 FTDs and $19,761 in total volume for December 2025. The convergence is an inherent limitation of moving average forecasting - it cannot capture trends or seasonality.

6. Additional Findings

  • Account structure: 74% of clients have a single trading account; average is 1.47 logins per client.
  • Total transaction volume: $4.55M USD across 7,763 transactions (including non-FTD deposits).
  • Temporal patterns: Transaction activity is concentrated on weekdays with a notable drop on weekends.
  • Data quality: After cleaning, referential integrity holds across all tables. Transaction amounts were converted to USD using ECB historical rates.

7. Limitations

  • Data covers Jan–Nov 2025 only; later months have fewer observations.
  • Demo registrations were excluded; only live registrations (Full, Light) are analyzed.
  • MA forecasts converge to a flat value and cannot model trends or seasonality. More advanced methods (ARIMA, Prophet) would be needed for longer-horizon predictions.
  • All amounts converted to USD using ECB rates — minor discrepancies possible for exotic currencies.